using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Collections;

using PHSRAG.Insight21.Admin;
using BO = PHSRAG.Insight21.BusinessObjects;
using DataAccess = PHSRAG.Utility.DataAccess;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using Utility = PHSRAG.Utility;
using PHSRAG.Insight21.CDS;


namespace PHSRAG.Insight21.ClinicalTrials
{
	/// <summary>
	/// BudgetAttachment abstracts the notion of an attachment. 
	/// If the attachment mode is electronic, then a Document object 
	/// tied to the BudgetAttachment is used to hold the electronic file. 
	/// </summary>
   public class BudgetAttachment : BO.SaveableBusinessObject
   {
      #region Constants
      private const string	BudgetAttachmentKey = "BudgetAttachment_5EA75003-51CD-4f4f-A440-FF8782AB72D2";
      /// <summary>
      /// Constant that specifies the 'BudgetAttachment' Database Table Name
      /// </summary>
      private const string BudgetAttachment_DataBase_Table_Name = "ClinicalTrialBudgetAttachment";

      /// <summary>
      /// Name of the stored procedure to retrieve a BudgetAttachment by its id
      /// </summary>
      private const string GetBudgetAttachmentById_SProc_Name = "GetClinicalTrialBudgetAttachmentById";

      /// <summary>
      /// Name of the stored procedure to save a BudgetAttachment
      /// </summary>
      private const string SaveBudgetAttachment_SProc_Name = "UpdateClinicalTrialBudgetAttachment";

      /// <summary>
      /// Name of the stored procedure to delete a BudgetAttachment
      /// </summary>
      private const string DeleteBudgetAttachment_SProc_Name = "DeleteClinicalTrialBudgetAttachment";

      /// <summary>
      /// Name of the stored procedure that returns all the protocol documents tied to a version
      /// </summary>
      private const string GetBudgetAttachments_SProc_Name = "GetClinicalTrialBudgetAttachments";
      #endregion

      #region Instance Variables (private)
      /// <summary>
      /// Holds the document object for electronic attachment
      /// </summary>
      private Document electronicFile;
      private DataRow dataRow;
      #endregion

      #region Constructors
      /// <summary>
      /// Constructs a new BudgetAttachment object based on the Cache Policy passed in as a parameter. 
      /// All the instance variables will be set to their default values.
      /// </summary>
      /// <param name="cachePolicy"></param>
      public BudgetAttachment(ICachePolicy cachePolicy) : base(cachePolicy)
      {
         DataContainer = new DataSet( );
         Utility.DataAccess.LoadDataSet( DataContainer, BudgetAttachment_DataBase_Table_Name, 
            Utility.DataAccess.GetConnectionString( ),
            GetBudgetAttachmentById_SProc_Name, Utility.DataAccess.BuildSqlParameterArray( "@id" ,SqlDbType.Int, 0 ) );
         dataRow = DataContainer.Tables[BudgetAttachment_DataBase_Table_Name].NewRow( );
      }

      /// <summary>
      /// Constructs a new BudgetAttachment object based on the Cache Policy passed in as a parameter. 
      /// All the instance variables will be set to the corresponding values specified in the Data Row 
      /// </summary>
      /// <param name="cachePolicy">Cache Policy which specifies where the BudgetAttachment object should be cached</param>
      /// <param name="dataRow">DataRow with columns as specified in BudgetAttachmentDatabaseTableColumns</param>
      public BudgetAttachment(ICachePolicy cachePolicy, DataRow row) : base (cachePolicy)
      {
         dataRow = row;
      }
      #endregion

      #region Properties
      /// <summary>
      /// Unique Id for this Budget Attachment
      /// </summary>
      public int Id
      {
         get
         {
            object o = dataRow["Id"];
            return (o == DBNull.Value) ? 0 : (int)o;
         }
      }

      /// <summary>
      /// Budget Id
      /// </summary>
      public int ClinicalTrialBudgetId
      {
         get
         {
            return (int)dataRow["ClinicalTrialBudgetId"];
         }
         set
         {
            dataRow["ClinicalTrialBudgetId"] = value;
         }
      }

      /// <summary>
      /// Person Id
      /// </summary>
      public int PersonId
      {
         get
         {
            return (int)dataRow["PersonId"];
         }
         set
         {
            dataRow["PersonId"] = value;
         }
      }

      /// <summary>
      /// Username for display
      /// </summary>
      public string User
      {
         get
         {
            User user = new User( CachePolicy );
            user.Load( Utility.DataAccess.GetConnectionString( ), PersonId );
            return user.FirstName + " " + user.LastName;
         }
      }

      /// <summary>
      /// Size
      /// </summary>
      public string Size
      {
         get
         {
            return dataRow["Size"].ToString( );
         }
      }

      /// <summary>
      /// Creationdate
      /// </summary>
      public DateTime CreationDate
      {
         get
         {
            return (DateTime)dataRow["DateCreated"];
         }
         set
         {
            dataRow["DateCreated"] = value;
         }
      }

      /// <summary>
      /// Name
      /// </summary>
      public string Name
      {
         get
         {
            return dataRow["FileName"].ToString( );
         }
      }

      /// <summary>
      /// Document Id
      /// </summary>
      public int DocumentId
      {
         get
         {
            return (int)dataRow["DocumentId"];
         }
      }

      /// <summary>
		/// Gets or sets the document id if the attachment is an electronic copy
		/// </summary>
		public Document ElectronicFile
		{
			get	{ return electronicFile; }
			set	
			{	
				electronicFile = value;
            int size = electronicFile.Blob.Length;
            dataRow["Size"] = string.Format( "{0}kb", Math.Round( ((decimal)size / 1024) ) );
			}
		}
		#endregion
		
		#region SaveableBusinessObject methods
		/// <summary>
		/// Cache this instance using the prevalent cache policy.
		/// </summary>
		public override void Cache()
		{
			CachePolicy.Cache(BudgetAttachmentKey, this);
		}

		/// <summary>
		/// Remove this instance from the cache using the prevalent cache policy.
		/// </summary>
		public override void UnCache()
		{
			CachePolicy.UnCache(BudgetAttachmentKey);
		}

		/// <summary>
		/// Loads the BudgetAttachment object and the contained Document object from the database with the specified id.
		/// </summary>
		/// <param name="connectionString">Information to connect to the database</param>
		/// <param name="args">BudgetAttachment Id of the document to be loaded</param>
		public override void Load(string connectionString, params object[] args)
		{
			if ( args.Length != 1 )
				throw new Exception("BudgetAttachment Load expects Id");

			try
			{
				DataSet BudgetAttachmentDetails = new DataSet();

				Utility.DataAccess.LoadDataSet(BudgetAttachmentDetails, BudgetAttachment_DataBase_Table_Name, connectionString, GetBudgetAttachmentById_SProc_Name, 
					Utility.DataAccess.BuildSqlParameterArray("@id" ,SqlDbType.Int, (int)args[0]));
				
				DataTable table = BudgetAttachmentDetails.Tables[BudgetAttachment_DataBase_Table_Name];
				DataRow dataRow = table.Rows[0];

				electronicFile = new Document(CachePolicy);
				electronicFile.Id = (int)dataRow["DocumentId"];
			}
			catch( Exception e)
			{
				Utility.ExceptionReport.WriteToEventLog ("Document Load", e, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
		}
		
		
		/// <summary>
		/// Saves the state of the BudgetAttachment object to the database. 
		/// The stored procedure inserts a new record if the id of the object is 0, 
		/// otherwise the procedure updates the record with the matching protocol document id
		/// </summary>
		/// <param name="sqlTransaction">The context in which the insert/update should be executed</param>
		public override void Save( string connectionString, params object[] args )
		{
         SqlConnection connection = new SqlConnection( connectionString );
         SqlTransaction transaction = null;
         try
			{
            connection.Open( );
            transaction = connection.BeginTransaction( );
            electronicFile.Save( transaction );
				// the stored procedure will determine whether to insert or update an existing Document object.
				object idObject = DataAccess.ExecuteScalar( transaction.Connection, transaction, SaveBudgetAttachment_SProc_Name, 
					DataAccess.BuildSqlParameterArray(
					"@id", SqlDbType.Int, Id,
               "@budgetId", SqlDbType.Int, ClinicalTrialBudgetId,
               "@personId", SqlDbType.Int, PersonId,
               "@size", SqlDbType.VarChar, Size,
					"@documentId", SqlDbType.Int, electronicFile.Id));
							
            transaction.Commit( );
				// set the ID to the returned object
   			dataRow["Id"] = Convert.ToInt32 (idObject);
			}
			catch( Exception e)
			{
				Utility.ExceptionReport.WriteToEventLog ("BudgetAttachment SaveObject", e, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
         finally
         {
            if ( connection.State != ConnectionState.Closed )
               connection.Close( );
            transaction.Dispose( );
         }
      }
		#endregion

		#region Static methods
		/// <summary>
		/// Loads all BudgetAttachments for a given ProtocolVersion.
		/// </summary>
		/// <param name="protocolVersionId">The ID of the ProtocolVersion</param>
		/// <param name="cachePolicy">The prevalent cache policy</param>
		/// <returns>Returns an arraylist of BudgetAttachments, if none exist, the array list count is zero.</returns>
		public static ArrayList GetDocuments( int budgetId, ICachePolicy cachePolicy )
		{
			ArrayList documents = new ArrayList();

			DataSet documentsDataSet = Utility.DataAccess.GetDataSet( Utility.DataAccess.ConnectionString, GetBudgetAttachments_SProc_Name,
				Utility.DataAccess.BuildSqlParameterArray
				("@budgetId", SqlDbType.Int, budgetId));

			// create a new protocol document for each row retrieved above
			DataTable table = documentsDataSet.Tables[0];

			for( int i=0; i < table.Rows.Count; ++i)
			{
				DataRow row = table.Rows[i];
				BudgetAttachment document = new BudgetAttachment( cachePolicy, row );
				documents.Add( document );
			}
			
			// return the array list of protocol documents.
			return documents;
		}

      /// <summary>
      /// Deletes attachments related to the passed in document Id
      /// </summary>
      /// <param name="documentId"></param>
      public static void DeleteAttachment( int documentId )
      {
         SqlConnection connection = new SqlConnection( DataAccess.GetConnectionString( ) );
         SqlTransaction transaction = null;
         try
         {
            connection.Open( );
            transaction = connection.BeginTransaction( );
            DataAccess.ExecuteNonQuery( connection, transaction, "DeleteClinicalTrialBudgetAttachment", 
               Utility.DataAccess.BuildSqlParameterArray( "@documentId", SqlDbType.Int, documentId ) );
            transaction.Commit( );
         }
         catch( Exception e )
         {
            Utility.ExceptionReport.WriteToEventLog ("BudgetAttachment Delete", e, true, Utility.ExceptionReport.ReportType.Error);
            throw;
         }
         finally
         {
            if ( connection.State != ConnectionState.Closed )
               connection.Close( );
            transaction.Dispose( );
         }
      }

      /// <summary>
      /// Cache Key uniquely identifies this object within the cache.
      /// </summary>
      /// <returns>the unique string for this object.</returns>
      public static string CacheKey()
      {
         return BudgetAttachmentKey;
      }
      #endregion
	}
}
